﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using Microsoft.Data.SqlClient;
using System.Data;
using static Azure.Core.HttpHeader;


namespace DAL
{
    public class SQLHelper
    {
        public static readonly string connString = StringSecurity.DESDecrypt(ConfigurationManager.ConnectionStrings["connString"].ToString());
        //private static string connString = ConfigurationManager.ConnectionStrings["connString"].ToString();

        /// <summary>
        /// 执行insert, update, delete类型的SQL语句
        /// </summary>
        /// <param name="sql">提交的sql语句，可以根据需要提交参数</param>
        /// <param name="param">参数数组（如果没有参数，请传递null）</param>
        /// <returns>返回受影响的行数</returns>
        public static int Update(string sql, SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (param != null)
            {
                cmd.Parameters.AddRange(param);  //添加参数数组
            }
            try
            {
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                string info = "执行 public int Update(string sql, SqlParameter[] param) 方法时发生异常: " + e.Message;
                //在这里写入日志...
                throw new Exception(info);
            }
            finally
            {
                conn.Close();
            }
        }



        /// <summary>
        ///返回单一结果的查询
        /// </summary>
        /// <param name="sql">提交的sql语句，可以根据需要提交参数</param>
        /// <param name="param">参数数组（如果没有参数，请传递null）</param>
        /// <returns>返回object类型</returns>
        public static object GetSingleResult(string sql, SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (param != null)
            {
                cmd.Parameters.AddRange(param);  //添加参数数组
            }
            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception e)
            {
                string info = "执行 public static object GetSingleResult(string sql, SqlParameter[] param) 方法时发生异常: " + e.Message;
                //在这里写入日志...
                throw new Exception(info);
            }
            finally
            {
                conn.Close();
            }
        }



        /// <summary>
        /// 返回一个结果集的查询
        /// </summary>
        /// <param name="sql">提交的sql语句，可以根据需要提交参数</param>
        /// <param name="param">参数数组（如果没有参数，请传递null）</param>
        /// <returns>返回 SqlDataReader类型</returns>
        public static SqlDataReader GetReader(string sql, SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (param != null)
            {
                cmd.Parameters.AddRange(param);  //添加参数数组
            }
            try
            {
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception e)
            {
                string info = "执行 public static SqlDataReader GetReader(string sql, SqlParameter[] param) 方法时发生异常: " + e.Message;
                //在这里写入日志...
                throw new Exception(info);
            }
        }

        /// <summary>
        /// 获取数据库服务器的时间
        /// </summary>
        /// <returns></returns>
        public static DateTime GetDBServerTime()
        {
            string sql = "select getdate()";
            return Convert.ToDateTime(GetSingleResult(sql, null));
        }

        public static bool UpDateByTran(List<string> sqlList)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            try
            {
                conn.Open();
                cmd.Transaction = conn.BeginTransaction();  //开启事务

                //循环执行SQL语句
                foreach(string sql in sqlList)
                {
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
                cmd.Transaction.Commit();  //提交事务（真正从数据库中修改了数据）
                return true;
            }
            catch (Exception ex)
            {
                if (cmd.Transaction != null)
                    cmd.Transaction.Rollback();  //回滚事务
                throw new Exception("调用事务方法出现错误：" + ex.Message);
            }
            finally
            {
                if (cmd.Transaction != null)
                    cmd.Transaction = null;  //清空事务
                conn.Close();
            }
        }

    }
}